package com.goscom.app.database;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

import de.greenrobot.dao.DaoLog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DBAdapter {
	private static final int DATABASE_VERSION = 1;
	private final Context ctx;
	private DatabaseHelper mDBHelper;
	public static SQLiteDatabase mDB;
	private static final String DATABASE_NAME = "deeset";
	private ContentValues content;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		private Context ctx;

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
			this.ctx = context;
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				executeSqlScript(ctx, db, "deeset.sql", true);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		public static int executeSqlScript(Context context, SQLiteDatabase db,
				String assetFilename, boolean transactional) throws IOException {
			byte[] bytes = readAsset(context, assetFilename);
			String sql = new String(bytes, "UTF-8");
			String[] lines = sql.split(";(\\s)*[\n\r]");
			int count;
			if (transactional) {
				count = executeSqlStatementsInTx(db, lines);
			} else {
				count = executeSqlStatements(db, lines);
			}
			// DaoLog.i("Executed " + count + " statements from SQL script '" +
			// assetFilename + "'");
			return count;
		}

		public static int executeSqlStatementsInTx(SQLiteDatabase db,
				String[] statements) {
			db.beginTransaction();
			try {
				int count = executeSqlStatements(db, statements);
				db.setTransactionSuccessful();
				return count;
			} finally {
				db.endTransaction();
			}
		}

		public static int executeSqlStatements(SQLiteDatabase db,
				String[] statements) {
			int count = 0;
			for (String line : statements) {
				line = line.trim();
				if (line.length() > 0) {
					db.execSQL(line);
					count++;
				}
			}
			return count;
		}

		/**
		 * Copies all available data from in to out without closing any stream.
		 * 
		 * @return number of bytes copied
		 */
		public static int copyAllBytes(InputStream in, OutputStream out)
				throws IOException {
			int byteCount = 0;
			byte[] buffer = new byte[4096];
			while (true) {
				int read = in.read(buffer);
				if (read == -1) {
					break;
				}
				out.write(buffer, 0, read);
				byteCount += read;
			}
			return byteCount;
		}

		public static byte[] readAllBytes(InputStream in) throws IOException {
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			copyAllBytes(in, out);
			return out.toByteArray();
		}

		public static byte[] readAsset(Context context, String filename)
				throws IOException {
			InputStream in = context.getResources().getAssets().open(filename);
			try {
				return readAllBytes(in);
			} finally {
				in.close();
			}
		}

		public static void logTableDump(SQLiteDatabase db, String tablename) {
			Cursor cursor = db.query(tablename, null, null, null, null, null,
					null);
			try {
				String dump = DatabaseUtils.dumpCursorToString(cursor);
				DaoLog.d(dump);
			} finally {
				cursor.close();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			onCreate(db);
		}
	}

	public DBAdapter(Context ctx) {
		this.ctx = ctx;
	}

	public DBAdapter open() {
		mDBHelper = new DatabaseHelper(ctx, DATABASE_NAME, null,
				DATABASE_VERSION);
		mDB = mDBHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDBHelper.close();
	}

	public void insertData(String strTable, ContentValues content) {
		mDB.insert(strTable, null, content);
	}

	public void updateUploadPic(String strTable, String strMerId, ContentValues content) {
        mDB.update(strTable, content, "merid = '" + strMerId + "'", null);
	}
	
	public void deleteData(String strTable, String strMerId) {
		mDB.delete(strTable, "merid=" + strMerId, null);
	}

	public void deleteDataAddOther(String strTable, String strMerId,
			String strActivity) {
		mDB.delete(strTable, "merid=" + strMerId + " AND activity='"
				+ strActivity + "'", null);
	}

	public void deleteDataContracted(String strTable, String strMerId,
			String strContracted) {
		mDB.delete(strTable, "merid=" + strMerId + " AND contractedid='"
				+ strContracted + "'", null);
	}

	public ArrayList<ContentValues> queryAllUser() {
		ArrayList<ContentValues> allTask = new ArrayList<ContentValues>();
		Cursor cur = mDB.query("USER", null, null, null, null, null, null);
		cur.moveToFirst();
		while (cur.isAfterLast() == false) {
			content = new ContentValues();
			getContentLogin(cur);
			allTask.add(content);
			cur.moveToNext();
		}
		cur.close();
		return allTask;
	}

	public ContentValues queryUserById(String strUserId) {
		Cursor cur = mDB.query("USER", null, "userid='" + strUserId + "'", null,
				null, null, null);
		cur.moveToFirst();
		content = new ContentValues();
		getContentLogin(cur);
		cur.close();
		return content;
	}
	
	public ArrayList<Integer> queryAllMerIdByUser(String strUserId) {
		ArrayList<Integer> allTask = new ArrayList<Integer>();
		Cursor cur = mDB.query("MER", null, "userid='" + strUserId + "'", null, null, null, null);
		cur.moveToFirst();
		while (cur.isAfterLast() == false) {
			String []strTemp = cur.getString(cur.getColumnIndexOrThrow("merid")).split("0");
			if (strTemp.length == 0) {
				allTask.add(0);
			} else {
				allTask.add(Integer.valueOf(strTemp[strTemp.length-1]));
			}
			cur.moveToNext();
		}
		cur.close();
		return allTask;
	}

	public int queryMerchandiser(String strTable, String strMerId) {
		int intCount = 0;
		Cursor cur = mDB.query(strTable, null, "merid=" + strMerId, null, null,
				null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			intCount++;
		}
		cur.close();
		return intCount;
	}

	public int queryMerchandiserAddOther(String strTable, String strMerId,
			String strActivity) {
		int intCount = 0;
		Cursor cur = mDB
				.query(strTable, null, "merid=" + strMerId + " AND activity='"
						+ strActivity + "'", null, null, null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			intCount++;
		}
		cur.close();
		return intCount;
	}

	public int queryMerchandiserContracted(String strTable, String strMerId,
			String strContractedId) {
		int intCount = 0;
		Cursor cur = mDB.query(strTable, null, "merid=" + strMerId
				+ " AND contractedid='" + strContractedId + "'", null, null,
				null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			intCount++;
		}
		cur.close();
		return intCount;
	}

	public final static String CONST_MERNAME = "MERNAME";

	public ContentValues queryDataByMerId(String strTable, String strMerId) {
		Cursor cur = mDB.query(strTable, null, "merid=" + strMerId, null, null,
				null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			content = new ContentValues();
			if (strTable.equals("MER")) {
				getContentMer(cur);
			}
			if (strTable.equals(DBAdapter.CONST_MERNAME)) {
				getContentMerName(cur);
			}
			if (strTable.equals("PERINFO")) {
				getContentPerInfo(cur);
			}
			if (strTable.equals("ADDRESS")) {
				getContentAddress(cur);
			}
			if (strTable.equals("VEHICLE")) {
				getContentVehicle(cur);
			}
			if (strTable.equals("CONEMER")) {
				getContentContactEmergency(cur);
			}
			if (strTable.equals("REHABILITATION")) {
				getContentRehabilitation(cur);
			}
			if (strTable.equals("ADDITIONAL")) {
				getContentAdditional(cur);
			}
			if (strTable.equals("BANKDETAILS")) {
				getContentBankDetails(cur);
			}
			if (strTable.equals("UPLOAD")) {
				getContentUpload(cur);
			}
			if (strTable.equals("OFFERLETTER")) {
				getContentOfferLetter(cur);
			}
			if (strTable.equals("CONTRACTUAL")) {
				getContentContractual(cur);
			}
		}
		cur.close();
		return content;
	}

	public ContentValues queryDataByMerAddOther(String strTable,
			String strMerId, String strActicity) {
		Cursor cur = mDB
				.query(strTable, null, "merid=" + strMerId + " AND activity='"
						+ strActicity + "'", null, null, null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			content = new ContentValues();
			if (strActicity.equals("referee1")
					|| strActicity.equals("referee2")) {
				getContentReferee(cur);
			} else {
				if (strActicity.equals("sectiona")
						|| strActicity.equals("sectionb")
						|| strActicity.equals("sectionc")
						|| strActicity.equals("sectiond")) {
					getContentMedicalHistory(cur);
				} else {
					getContentStoreInduction(cur);
				}
			}
		}
		cur.close();
		return content;
	}

	public ArrayList<String> queryQuesByMerAddOther(String strTable,
			String strSection) {
		ArrayList<String> arrlst = new ArrayList<String>();
		Cursor cur = mDB.query(strTable, new String[] { "content" },
				"section='" + strSection + "'", null, null, null, null);

		cur.moveToFirst();
		while (cur.isAfterLast() == false) {
			arrlst.add(cur.getString(0));
			cur.moveToNext();
		}

		cur.close();
		return arrlst;
	}

	public ContentValues queryDataByMerContracted(String strTable,
			String strMerId, String strContracted) {
		Cursor cur = mDB.query(strTable, null, "merid=" + strMerId
				+ " AND contractedid='" + strContracted + "'", null, null,
				null, null);
		cur.moveToFirst();
		if (cur.isAfterLast() == false) {
			content = new ContentValues();
			getContentContractedHours(cur);
		}
		cur.close();
		return content;
	}

	public ArrayList<ContentValues> queryListByMerContracted(String strTable,
			String strMerId, String strActivity) {
		ArrayList<ContentValues> arrlst = new ArrayList<ContentValues>();
		Cursor cur = mDB.query(strTable, null, "merid = '" + strMerId
				+ "' AND activity = '" + strActivity + "'", null, null, null,
				null);

		cur.moveToFirst();
		while (cur.isAfterLast() == false) {
			content = new ContentValues();
			getContentContractedHours(cur);
			arrlst.add(content);
			Log.i("#", "@");
			cur.moveToNext();
		}
		cur.close();
		return arrlst;
	}

	public ArrayList<String> queryListStoreAndRetailer(String search) {
		ArrayList<String> arrlst = new ArrayList<String>();
		Cursor cur = null;
		if (search == null) {
			cur = mDB.query("STOREID", new String[] { "name" }, null, null,
					null, null, null);
		} else {
			if (search != null) {
				Log.i("search", search);
			}
			cur = mDB.query("STOREID", new String[] { "name" }, "name like '%"
					+ search + "%'", null, null, null, null);
		}

		cur.moveToFirst();
		while (cur.isAfterLast() == false) {
			arrlst.add(cur.getString(0));
			cur.moveToNext();
		}
		cur.close();
		return arrlst;
	}

	public void getContentLogin(Cursor cur) {
		content.put("userid",
				cur.getString(cur.getColumnIndexOrThrow("userid")));
		content.put("username",
				cur.getString(cur.getColumnIndexOrThrow("username")));
		content.put("password",
				cur.getString(cur.getColumnIndexOrThrow("password")));
	}

	public void getContentMer(Cursor cur) {
		content.put("userid",
				cur.getString(cur.getColumnIndexOrThrow("userid")));
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
	}

	public void getContentMerName(Cursor cur) {

		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("title", cur.getString(cur.getColumnIndexOrThrow("title")));
		content.put("forename",
				cur.getString(cur.getColumnIndexOrThrow("forename")));
		content.put("surname",
				cur.getString(cur.getColumnIndexOrThrow("surname")));
		content.put("gender",
				cur.getString(cur.getColumnIndexOrThrow("gender")));
		content.put("area", cur.getString(cur.getColumnIndexOrThrow("area")));
		content.put("amname",
				cur.getString(cur.getColumnIndexOrThrow("amname")));
		content.put("tlname",
				cur.getString(cur.getColumnIndexOrThrow("tlname")));
		content.put("date", cur.getString(cur.getColumnIndexOrThrow("date")));

	}

	public void getContentPerInfo(Cursor cur) {
		try {
			content.put("merid",
					cur.getString(cur.getColumnIndexOrThrow("merid")));
			content.put("telnum",
					cur.getString(cur.getColumnIndexOrThrow("telnum")));
			content.put("control",
					cur.getString(cur.getColumnIndexOrThrow("control")));
			content.put("email",
					cur.getString(cur.getColumnIndexOrThrow("email")));
			content.put("mobnum",
					cur.getString(cur.getColumnIndexOrThrow("mobnum")));
			content.put("nation",
					cur.getString(cur.getColumnIndexOrThrow("nation")));
			content.put("birth",
					cur.getString(cur.getColumnIndexOrThrow("birth")));
			content.put("insur",
					cur.getString(cur.getColumnIndexOrThrow("insur")));
			content.put("strdate",
					cur.getString(cur.getColumnIndexOrThrow("strdate")));
			content.put("jobtit",
					cur.getString(cur.getColumnIndexOrThrow("jobtit")));
			content.put("ckcA",
					cur.getString(cur.getColumnIndexOrThrow("ckcA")));
			content.put("ckcB",
					cur.getString(cur.getColumnIndexOrThrow("ckcB")));
			content.put("ckcC",
					cur.getString(cur.getColumnIndexOrThrow("ckcC")));
			content.put("ckcD",
					cur.getString(cur.getColumnIndexOrThrow("ckcD")));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void getContentAddress(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("numstreet",
				cur.getString(cur.getColumnIndexOrThrow("numstreet")));
		content.put("city", cur.getString(cur.getColumnIndexOrThrow("city")));
		content.put("county",
				cur.getString(cur.getColumnIndexOrThrow("county")));
		content.put("postcode",
				cur.getString(cur.getColumnIndexOrThrow("postcode")));
	}

	public void getContentVehicle(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("licence",
				cur.getString(cur.getColumnIndexOrThrow("licence")));
		content.put("pointlic",
				cur.getString(cur.getColumnIndexOrThrow("pointlic")));
	}

	public void getContentReferee(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("name", cur.getString(cur.getColumnIndexOrThrow("name")));
		content.put("role", cur.getString(cur.getColumnIndexOrThrow("role")));
		content.put("telephone",
				cur.getString(cur.getColumnIndexOrThrow("telephone")));
		content.put("conadd",
				cur.getString(cur.getColumnIndexOrThrow("conadd")));
		content.put("activity",
				cur.getString(cur.getColumnIndexOrThrow("activity")));
	}

	public void getContentContactEmergency(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("addtelnum",
				cur.getString(cur.getColumnIndexOrThrow("addtelnum")));
	}

	public void getContentRehabilitation(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("convicted",
				cur.getString(cur.getColumnIndexOrThrow("convicted")));
		content.put("detail",
				cur.getString(cur.getColumnIndexOrThrow("detail")));
	}

	public void getContentAdditional(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("languages",
				cur.getString(cur.getColumnIndexOrThrow("languages")));
		content.put("qualifications",
				cur.getString(cur.getColumnIndexOrThrow("qualifications")));
		content.put("skills",
				cur.getString(cur.getColumnIndexOrThrow("skills")));
		content.put("hobbies",
				cur.getString(cur.getColumnIndexOrThrow("hobbies")));
	}

	public void getContentStoreInduction(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("checkall",
				cur.getString(cur.getColumnIndexOrThrow("checkall")));
		content.put("check1",
				cur.getString(cur.getColumnIndexOrThrow("check1")));
		content.put("check2",
				cur.getString(cur.getColumnIndexOrThrow("check2")));
		content.put("activity",
				cur.getString(cur.getColumnIndexOrThrow("activity")));
	}

	public void getContentBankDetails(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("nameacc",
				cur.getString(cur.getColumnIndexOrThrow("nameacc")));
		content.put("accnum",
				cur.getString(cur.getColumnIndexOrThrow("accnum")));
		content.put("sortcode",
				cur.getString(cur.getColumnIndexOrThrow("sortcode")));
		content.put("refnum",
				cur.getString(cur.getColumnIndexOrThrow("refnum")));
	}

	public void getContentUpload(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("name1", cur.getString(cur.getColumnIndexOrThrow("name1")));
		content.put("image1", cur.getString(cur.getColumnIndexOrThrow("image1")));
		content.put("upload1", cur.getString(cur.getColumnIndexOrThrow("upload1")));
		content.put("name2", cur.getString(cur.getColumnIndexOrThrow("name2")));
		content.put("image2", cur.getString(cur.getColumnIndexOrThrow("image2")));
		content.put("upload2", cur.getString(cur.getColumnIndexOrThrow("upload2")));
		content.put("name3", cur.getString(cur.getColumnIndexOrThrow("name3")));
		content.put("image3", cur.getString(cur.getColumnIndexOrThrow("image3")));
		content.put("upload3", cur.getString(cur.getColumnIndexOrThrow("upload3")));
		content.put("name4", cur.getString(cur.getColumnIndexOrThrow("name4")));
		content.put("image4", cur.getString(cur.getColumnIndexOrThrow("image4")));
		content.put("upload4", cur.getString(cur.getColumnIndexOrThrow("upload4")));
	}

	public void getContentOfferLetter(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("comdate",
				cur.getString(cur.getColumnIndexOrThrow("comdate")));
		content.put("contracted",
				cur.getString(cur.getColumnIndexOrThrow("contracted")));
		content.put("noncontracted",
				cur.getString(cur.getColumnIndexOrThrow("noncontracted")));
		content.put("addsto",
				cur.getString(cur.getColumnIndexOrThrow("addsto")));
		content.put("agree", cur.getString(cur.getColumnIndexOrThrow("agree")));
		content.put("pay", cur.getString(cur.getColumnIndexOrThrow("pay")));
	}

	public void getContentContractedHours(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("activity",
				cur.getString(cur.getColumnIndexOrThrow("activity")));
		content.put("contractedid",
				cur.getString(cur.getColumnIndexOrThrow("contractedid")));
		content.put("storel",
				cur.getString(cur.getColumnIndexOrThrow("storel")));
		content.put("jobskill",
				cur.getString(cur.getColumnIndexOrThrow("jobskill")));
		content.put("intstorel",
				cur.getString(cur.getColumnIndexOrThrow("intstorel")));
		content.put("intjobskill",
				cur.getString(cur.getColumnIndexOrThrow("intjobskill")));
		content.put("mon", cur.getString(cur.getColumnIndexOrThrow("mon")));
		content.put("tue", cur.getString(cur.getColumnIndexOrThrow("tue")));
		content.put("wed", cur.getString(cur.getColumnIndexOrThrow("wed")));
		content.put("thu", cur.getString(cur.getColumnIndexOrThrow("thu")));
		content.put("fri", cur.getString(cur.getColumnIndexOrThrow("fri")));
		content.put("sat", cur.getString(cur.getColumnIndexOrThrow("sat")));
		content.put("sun", cur.getString(cur.getColumnIndexOrThrow("sun")));
		content.put("search",
				cur.getString(cur.getColumnIndexOrThrow("search")));
	}

	public void getContentMedicalHistory(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("activity",
				cur.getString(cur.getColumnIndexOrThrow("activity")));
		content.put("ans1", cur.getString(cur.getColumnIndexOrThrow("ans1")));
		content.put("ans2", cur.getString(cur.getColumnIndexOrThrow("ans2")));
		content.put("ans3", cur.getString(cur.getColumnIndexOrThrow("ans3")));
		content.put("ans4", cur.getString(cur.getColumnIndexOrThrow("ans4")));
		content.put("ans5", cur.getString(cur.getColumnIndexOrThrow("ans5")));
		content.put("ans6", cur.getString(cur.getColumnIndexOrThrow("ans6")));
		content.put("ans7", cur.getString(cur.getColumnIndexOrThrow("ans7")));
		content.put("ans8", cur.getString(cur.getColumnIndexOrThrow("ans8")));
		content.put("ans9", cur.getString(cur.getColumnIndexOrThrow("ans9")));
		content.put("ans10", cur.getString(cur.getColumnIndexOrThrow("ans10")));
		content.put("ans11", cur.getString(cur.getColumnIndexOrThrow("ans11")));
		content.put("ans12", cur.getString(cur.getColumnIndexOrThrow("ans12")));
		content.put("ans13", cur.getString(cur.getColumnIndexOrThrow("ans13")));
	}

	public void getContentContractual(Cursor cur) {
		content.put("merid", cur.getString(cur.getColumnIndexOrThrow("merid")));
		content.put("sign", cur.getBlob(cur.getColumnIndexOrThrow("sign")));
		content.put("upload", cur.getString(cur.getColumnIndexOrThrow("upload")));
	}

	public void getContentStoreName(Cursor cur) {
		content.put("storeid",
				cur.getString(cur.getColumnIndexOrThrow("storeid")));
		content.put("name", cur.getString(cur.getColumnIndexOrThrow("name")));
	}
}
